IF EXISTS (SELECT * FROM sysobjects WHERE name = 'GetDBConnectionString' AND type = 'P')
     DROP PROCEDURE dbo.GetDBConnectionString
GO

CREATE PROCEDURE dbo.GetDBConnectionString
     @SrvrInstncId     int, 
     @DatabaseName     varchar(128)    = NULL,
     @ConnectionString varchar(8000)   = NULL OUTPUT,
     @CipherTextString varbinary(1024) = NULL OUTPUT
AS
/*
 *
 * GetDBConnectionString
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      A Cleartext connection string
 *
 * Note(s):      This version is a stub, it contains no differentiation logic
 *               for any of the different server types and currently only builds
 *               trusted connection strings for MSSQL.
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 09/29/2010, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     -- Log sproc use.  Starting
     EXEC dbo.InsEventLog @EventLogTypeId = 1, -- Informational
                          @LogByApp       = 'GetDBConnectionString',
                          @LogByDesc      = 'Standard',
                          @LogText        = 'GetDBConnectionString Starting',
                          @MiscInt1       = @SrvrInstncId,
                          @MiscVC1        = @DatabaseName

     DECLARE @SrvrInstncTypeId     int,
             @SrvrInstncName       varchar(128),
             @OLEDBProviderName    varchar(128)

     SET @CipherTextString = NULL
     
     IF ( @DatabaseName IS NULL )
     BEGIN
          SET @DatabaseName = 'master'
     END

     -- Get basic server information
     SELECT @SrvrInstncName      = i.SrvrInstncFullName,
            @SrvrInstncTypeId    = t.SrvrInstncTypeId,
            @OLEDBProviderName   = t.OLEDBProviderName 
     FROM   dbo.SrvrInstnc i INNER JOIN dbo.SrvrInstncType t
               ON i.SrvrInstncTypeId = t.SrvrInstncTypeId 
     WHERE  SrvrInstncId = @SrvrInstncId 

     SELECT @ConnectionString = dbo.GenConnectionString( 1, @SrvrInstncName, @DatabaseName, NULL, NULL, @OLEDBProviderName, NULL )
     
     -- Log ending..
     EXEC dbo.InsEventLog @EventLogTypeId = 1, -- Informational
                          @LogByApp       = 'GetDBConnectionString',
                          @LogText        = 'GetDBConnectionString Ending',
                          @LogByDesc      = 'Standard',
                          @MiscInt1       = @SrvrInstncId,
                          @MiscInt2       = NULL, -- @SrvrInstncCredTypeId,
                          @MiscVC1        = @DatabaseName

     -- Return it..
     SELECT ConnectionString = @ConnectionString,
            CipherTextString = @CipherTextString 
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Get a conenction string for a specified server instance id.  This is the standard version that does not handle any form of encryption.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = GetDBConnectionString;
GO
